﻿
CREATE proc [dbo].[GetContactsWithDetails]
	@ContactID		int				= null,
	@SearchKeyword	nvarchar(50)	= null,
	@LastName		nvarchar(50)	= null,
	@ZipCode		nvarchar(20)	= null
as
begin
	set nocount on;
	set transaction isolation level Read Uncommitted;

	declare @contacts table (
		ContactID int
	);

	

	-- 1. Resultset - Contacts Basis-Data
	with contacts
	as
	(
	select	c.*
	from	dbo.datContacts c
	where	(c.ContactID = @ContactID
				or @ContactID is null)
			and (c.LastName = @LastName
				or @LastName is null)
			and (c.SearchKeyword = @SearchKeyword
				or @SearchKeyword is null)
	),adresses
	as
	(
		select	distinct ca.ContactID
		from	dbo.datAddress a
				inner join mnContactsAddress ca on
					ca.AddressID = a.AddressID
				inner join dbo.Postleitzahl p on
					p.EPLZ = a.ZipCodeID
		where	(p.PLZ = @ZipCode)
		union all
		select	c.ContactID
		from	datContacts c
		where	@ZipCode is null
	)
	insert into @contacts (ContactID)
	select	c.ContactID
	from	contacts c
			inner join adresses a on
				a.ContactID = c.ContactID
	where	(c.SearchKeyword = @SearchKeyword
				or @SearchKeyword is null)
			and (c.LastName = @LastName
				or @LastName is null)
			and (c.ContactID = @ContactID
				or @ContactID is null);
	
	select	c.ContactID, 
			c.IsOrganisation, 
			c.FirstName, 
			c.LastName, 
			c.CompanyName, 
			c.CompanyNameAdditional, 
			c.Sex, 
			c.LegalFormKey, 
			c.VatRegNo, 
			c.Notice, 
			c.Birthday, 
			c.SearchKeyword, 
			c.Department, 
			c.CreateOn, 
			c.CreateBy, 
			cast(convert(nvarchar(25),c.LastChangedOn, 120) as datetime) 'LastChangedOn', 
			c.LastChangedBy
	from	dbo.datContacts c
			inner join @contacts cl on
				cl.ContactID = c.ContactID
				

	-- 2. Resultset - Contacts - Address-Data
	select	ca.ContactID,
			a.AddressID,
			atype.AddressType,
			a.IsActive,
			a.Street,
			a.StreetAdditional,
			p.plz 'ZipCode',
			p.Ort 'City',
			p.Zone,
			p.LKZ 'Country'
	from	dbo.datAddress a
			inner join dbo.mnContactsAddress ca on
				ca.AddressID = a.AddressID
			inner join dbo.domAddressType atype on
				atype.AddressTypeID = ca.AddressTypeID
			inner join dbo.Postleitzahl p on
				p.EPLZ = a.ZipCodeID
			inner join @contacts c on
				c.ContactID = ca.ContactID

	
	-- 3. Resultset - Contacts - Opportunity Data
	select	co.ContactID,
			o.OpportunityID,
			op.OpportunityType,
			o.Opportunity,
			o.IsActive
	from	datOpportunity o
			inner join dbo.mnContactsOpportunity co on
				co.OpportunityID = o.OpportunityID
			inner join dbo.domOpportunity op on
				op.OpportunityTypeID = co.OpportunityTypeID
			inner join @contacts c on
				c.ContactID = co.ContactID

	-- 4. Resultset - Contact - Principals
	select	cp.ContactID,
			cp.PrincipalID,
			p.IsOrganisation,
			case when p.IsOrganisation != 0 then p.CompanyName else p.FirstName + ' ' + p.LastName end  'PrincipalName',
			tel.Telefon,
			cp.PhoneExtension,
			cp.Fax,
			cp.Job,
			bt.BerufstitelBez 'JobTitle',
			f.FunktionsBez 'Function'
	from	mnContactsPrincipals cp
			inner join dbo.datContacts p on
				p.ContactID = cp.PrincipalID
			inner join @contacts c on
				c.ContactID = cp.ContactID
			left join dbo.Berufstitel bt on
				bt.BerufstitelKey = cp.JobtitleKey
			left join Funktion f on
				f.FunktionsKey = cp.FunctionKey
			left join (
				select	cp.ContactID,
						op.Opportunity 'Telefon',
						Row_Number() over (Partition by cp.ContactID order by op.OpportunityID) 'RowNumber'
				from	dbo.datOpportunity op
						inner join dbo.mnContactsOpportunity cp on
							cp.OpportunityID = op.OpportunityID
							and cp.OpportunityTypeID = 1
				) tel on
					tel.ContactID = cp.PrincipalID
					and tel.RowNumber = 1
				



	-- 5. Resultset - Title
	select	ct.ContactID,
			ct.TitelID 'TitleID',
			ti.TitelBez 'Title'
	from	dbo.mnContactTitle ct
			inner join dbo.Titel ti on
				ti.TitelKey = ct.TitelID
			inner join @contacts c on
				c.ContactID = ct.ContactID

	-- 6. Resultset - Principals -> Contacts
	select	cp.ContactID,
			cp.PrincipalID,
			p.IsOrganisation,
			case when p.IsOrganisation != 0 then p.CompanyName else ltrim(coalesce(p.FirstName,'') + ' ' + coalesce(p.LastName,'')) end  'PrincipalName',
			tel.Telefon,
			cp.PhoneExtension,
			cp.Fax,
			cp.Job,
			bt.BerufstitelBez 'JobTitle',
			f.FunktionsBez 'Function'
	from	mnContactsPrincipals cp
			inner join dbo.datContacts p on
				p.ContactID = cp.ContactID
			inner join @contacts c on
				c.ContactID = cp.PrincipalID
			left join dbo.Berufstitel bt on
				bt.BerufstitelKey = cp.JobtitleKey
			left join Funktion f on
				f.FunktionsKey = cp.FunctionKey
			left join (
				select	cp.ContactID,
						op.Opportunity 'Telefon',
						Row_Number() over (Partition by cp.ContactID order by op.OpportunityID) 'RowNumber'
				from	dbo.datOpportunity op
						inner join dbo.mnContactsOpportunity cp on
							cp.OpportunityID = op.OpportunityID
							and cp.OpportunityTypeID = 1
				) tel on
					tel.ContactID = cp.PrincipalID
					and tel.RowNumber = 1

end
